import java.io.File;
import java.sql.Date;
import java.sql.Time;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import org.w3c.dom.*;
import javax.xml.parsers.*;

public class Assignment3 {
    
    public static void main(String[] args) {
    	
        processEmployeesFile();
        processFlightDataFile();
        processReservedSeatsFile();
        
        DataAbstractionLayer.getInstance().commitAndClose();
    }
    
    public Assignment3() {
    	
    }


    static void processEmployeesFile() {
        final String EmployeesFilePath = "Employees.xml";
        
        DataAbstractionLayer dal = DataAbstractionLayer.getInstance();
        
        System.out.println("Processing " + EmployeesFilePath + ":");
        try {
            DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
            Document doc = docBuilder.parse(new File(EmployeesFilePath));
            doc.normalizeDocument();

            // iterate through the employees, importing their info into the database
            NodeList employeeElements = doc.getElementsByTagName("Employee");
            for (int i = 0; i < employeeElements.getLength(); i++) {
                
            	Employee ourEmp = processEmployee((Element)employeeElements.item(i));
                dal.addEmployee(ourEmp);
            }
        } catch(Exception e) {
        	e.printStackTrace();
        }
    }
    
    static void processFlightDataFile() {
        final String FlightDataFilePath = "FlightData.xml";
        
        DataAbstractionLayer dal = DataAbstractionLayer.getInstance();
        
        System.out.println("Processing " + FlightDataFilePath + ":");
        try {
            DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
            Document doc = docBuilder.parse(new File(FlightDataFilePath));
            doc.normalizeDocument();

            // iterate through the flights, importing their info into the database
            NodeList flightElements = doc.getElementsByTagName("Flight");
            for (int i = 0; i < flightElements.getLength(); i++) {

            	Element curFlight = (Element)flightElements.item(i);
            	
            	PlaneModel pm = processPlaneModel(curFlight);
            	Airport apFrom = processFromAirport(curFlight);
            	Airport apTo = processToAirport(curFlight);
            	Airline al = processAirline(curFlight);
            	
            	FlightSegment fs = processFlightSegment(curFlight, apFrom, apTo, al, pm);
            	FlightDate fd = processFlightDate(curFlight, fs);
            	
            	fs.setPlaneModel(dal.addPlaneModel(pm));
            	fs.setFromAirport(dal.addAirport(apFrom));
            	fs.setToAirport(dal.addAirport(apTo));
            	fs.setAirline(dal.addAirline(al));
            	
            	fd.setFlightSegment(dal.addFlightSegment(fs));
            	dal.addFlightDate(fd);
            	
            }
        } catch(Exception e) {
        	e.printStackTrace();
        }
    }
    
    static void processReservedSeatsFile() {
        final String ReservedSeatsFilePath = "ReservedSeats.xml";
        System.out.println("Processing " + ReservedSeatsFilePath + ":"); 
        
        DataAbstractionLayer dal = DataAbstractionLayer.getInstance();
        
        try {
            DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
            Document doc = docBuilder.parse(new File(ReservedSeatsFilePath));
            doc.normalizeDocument();

            // iterate through the reservations, importing their info into the database
            NodeList seatElements = doc.getElementsByTagName("Seat");
            for (int i = 0; i < seatElements.getLength(); i++) {

            	Element curSeat = (Element)seatElements.item(i);
            	
            	Customer cust = processCustomer(curSeat);
            	Passenger pas = processPassenger(curSeat);
            	FlightSegment fs = processFlightSegment(curSeat);
            	FlightDate fd = processSeatFlightDate(curSeat, fs);
            	FlightReservation fr = processFlightReservation(curSeat, cust);
            	
            	Seat seat = processSeat(curSeat, fd, pas, fr);
            	
            	fr.setCustomer(dal.addCustomer(cust));
            	seat.setPassenger(dal.addPassenger(pas));
            	seat.setReservation(dal.addReservation(fr));
            	dal.addSeat(seat);
            	
            }
        } catch(Exception e) {
        	e.printStackTrace();
        }
    }
    
    static Employee processEmployee(Element employeeElement) {
    	
    	// extract the employee's info
        String firstName = employeeElement.getElementsByTagName("FirstName").item(0).getTextContent();
        String lastName = employeeElement.getElementsByTagName("LastName").item(0).getTextContent();
        boolean isEmployed = Boolean.parseBoolean(employeeElement.getElementsByTagName("isEmployed").item(0).getTextContent());
        boolean isBookingAgent = Boolean.parseBoolean(employeeElement.getElementsByTagName("isBookingAgent").item(0).getTextContent());
        
        return new Employee(firstName,lastName,isEmployed,isBookingAgent);
    	
    }
    
    static PlaneModel processPlaneModel(Element flightElement) {
    	
    	// extract the PlaneModel's info
        String planeModel = flightElement.getElementsByTagName("PlaneModel").item(0).getTextContent();
        int planeRows = Integer.parseInt(flightElement.getElementsByTagName("PlaneRows").item(0).getTextContent());
        int planeCols = Integer.parseInt(flightElement.getElementsByTagName("PlaneCols").item(0).getTextContent());
        
        return new PlaneModel(planeModel,planeRows, planeCols);
    	
    }
    
    static Airport processFromAirport(Element flightElement) {
    	
    	// extract the Airport's info
        String code = flightElement.getElementsByTagName("FromAirport").item(0).getTextContent();
        String city = flightElement.getElementsByTagName("FromCity").item(0).getTextContent();
        
        return new Airport(code,city);
    }
    
    static Airport processToAirport(Element flightElement) {
    	
    	// extract the Airport's info
        String code = flightElement.getElementsByTagName("ToAirport").item(0).getTextContent();
        String city = flightElement.getElementsByTagName("ToCity").item(0).getTextContent();
        
        return new Airport(code,city);
    }

    static Airline processAirline(Element flightElement) {
    	
    	// extract the Airline's info
        String code = flightElement.getElementsByTagName("AirlineCode").item(0).getTextContent();
        String name = flightElement.getElementsByTagName("Airline").item(0).getTextContent();
        
        return new Airline(code,name);
    }
    
    static FlightSegment processFlightSegment(Element flightElement, Airport fromAirport, Airport toAirport, 
    		Airline airline, PlaneModel planeModel) {
    	
    	// extract the Airline's info
        String flightNo = flightElement.getElementsByTagName("FlightNo").item(0).getTextContent();
        double price = Double.parseDouble(flightElement.getElementsByTagName("Price").item(0).getTextContent());
        String departureTimeString = flightElement.getElementsByTagName("DepartureTime").item(0).getTextContent();
        String arrivalTimeString = flightElement.getElementsByTagName("ArrivalTime").item(0).getTextContent();
        DateFormat df = new SimpleDateFormat("hh:mm:ss a");
        Time departureTime = null;
        Time arrivalTime = null;
        try {
            departureTime = new Time(df.parse(departureTimeString).getTime());
            arrivalTime = new Time(df.parse(arrivalTimeString).getTime());
        } catch(java.text.ParseException pe) {
            System.out.println("ERROR: failed to parse time");
        }
        
        return new FlightSegment(flightNo, airline, price,departureTime,arrivalTime,fromAirport,toAirport,planeModel);
    }
    
    static FlightDate processFlightDate(Element flightElement, FlightSegment fs) {
    	Date departureDate = convertXMLDate(flightElement.getElementsByTagName("DepartureDate").item(0).getTextContent());
    	
    	return new FlightDate(fs,departureDate);
    }
    
    static Customer processCustomer(Element seatElement) {
    	// extract the customer's info
        String firstName = seatElement.getElementsByTagName("CustFirstName").item(0).getTextContent();
        String lastName = seatElement.getElementsByTagName("CustLastName").item(0).getTextContent();
        String address = seatElement.getElementsByTagName("CustAddr").item(0).getTextContent();
        String creditCard = seatElement.getElementsByTagName("CustCC").item(0).getTextContent();
        
        return new Customer(creditCard,firstName,lastName,address);
    }
    
    static Passenger processPassenger(Element seatElement) {
    	// extract the passenger's info
        String firstName = seatElement.getElementsByTagName("PassFirstName").item(0).getTextContent();
        String lastName = seatElement.getElementsByTagName("PassLastName").item(0).getTextContent();
        String passportNo = seatElement.getElementsByTagName("PassPassportNo").item(0).getTextContent();
        String passportCount = seatElement.getElementsByTagName("PassPassportCountry").item(0).getTextContent();
        
        return new Passenger(passportNo,passportCount,firstName,lastName);
    }
    
    static FlightSegment processFlightSegment(Element seatElement) {
    	String airlineCode = seatElement.getElementsByTagName("AirlineCode").item(0).getTextContent();
        String flightNo = seatElement.getElementsByTagName("FlightNo").item(0).getTextContent();
        
        return DataAbstractionLayer.getInstance().getFlightSegmentFromFlightNoAndAirlineCode(flightNo, airlineCode);
        
    }
    
    static FlightDate processSeatFlightDate(Element seatElement, FlightSegment fs) {
    	Date departureDate = convertXMLDate(seatElement.getElementsByTagName("DepartureDate").item(0).getTextContent());
    	
    	return DataAbstractionLayer.getInstance().getFlightDateFromFSAndDate(fs, departureDate);
    }
    
    
    static FlightReservation processFlightReservation(Element seatElement, Customer cust) {
    
    	String firstName = seatElement.getElementsByTagName("BAFirstName").item(0).getTextContent();
        String lastName = seatElement.getElementsByTagName("BALastName").item(0).getTextContent();
        double totalPrice = Double.parseDouble(seatElement.getElementsByTagName("TotalPrice").item(0).getTextContent());
        
        Employee bookingAgent = DataAbstractionLayer.getInstance().getEmployeeFromFirstAndLastName(firstName, lastName);
        
        return new FlightReservation(cust,bookingAgent,totalPrice);
    }
    
    static Seat processSeat(Element seatElement, FlightDate fd, Passenger pas, FlightReservation fr) {
    	int seatRow = Integer.parseInt(seatElement.getElementsByTagName("SeatRow").item(0).getTextContent());
        int seatCol = Integer.parseInt(seatElement.getElementsByTagName("SeatCol").item(0).getTextContent());
        
        return new Seat(pas,fr,fd,seatRow,seatCol);
    }
    
    /**
     * Changes a dd/mm/yyyy date to an SQL date value
     * @param badDate the string in dd/mm/yyyy format
     * @return an sql Date object
     */
    static Date convertXMLDate(String badDate) {
    	String[] dateParts = badDate.split("/");
    	
    	String year = dateParts[2];
    	
    	if(year.length() < 4) {
    		year = "20" + year;
    	}
    	
    	return Date.valueOf(year + "-" + dateParts[1] + "-" + dateParts[0]);
    }

}

